import pandas as pd
import plotly.express as px
import numpy as np
import plotly.graph_objects as go
import sqlite3 as sq3
con = sq3.connect('/home/jupyter-jkulaka1/opioid.db')
annual = pd.read_sql_query("SELECT * from annual", con)
land = pd.read_sql_query("SELECT * from land", con)
population = pd.read_sql_query("SELECT * from population", con)
con.close
annual.loc[(annual.BUYER_STATE == "AR") & (annual.BUYER_COUNTY == "MONTGOMERY"), "countyfips"] = "05097"
annual = annual[annual.BUYER_COUNTY.str.contains("NA") ==False]
land_area = land[["Areaname", "STCOU", "LND110210D"]]
land_area = land_area.rename(columns={"STCOU": "countyfips"})
county_info = population.merge(land_area, how = "left", on = "countyfips")
county_info
| BUYER_COUNTY | BUYER_STATE | countyfips | STATE | COUNTY | county_name | NAME | variable | year | population | Areaname | LND110210D | ||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 0 | 1 | AUTAUGA | AL | 01001 | 1 | 1 | Autauga | Autauga County, Alabama | B01003_001 | 2006 | 51328 | Autauga, AL | 594.44 |
| 1 | 2 | BALDWIN | AL | 01003 | 1 | 3 | Baldwin | Baldwin County, Alabama | B01003_001 | 2006 | 168121 | Baldwin, AL | 1589.78 |
| 2 | 3 | BARBOUR | AL | 01005 | 1 | 5 | Barbour | Barbour County, Alabama | B01003_001 | 2006 | 27861 | Barbour, AL | 884.88 |
| 3 | 4 | BIBB | AL | 01007 | 1 | 7 | Bibb | Bibb County, Alabama | B01003_001 | 2006 | 22099 | Bibb, AL | 622.58 |
| 4 | 5 | BLOUNT | AL | 01009 | 1 | 9 | Blount | Blount County, Alabama | B01003_001 | 2006 | 55485 | Blount, AL | 644.78 |
| ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... |
| 28260 | 28261 | WASHAKIE | WY | 56043 | 56 | 43 | Washakie | Washakie County, Wyoming | B01003_001 | 2014 | 8444 | Washakie, WY | 2238.55 |
| 28261 | 28262 | WESTON | WY | 56045 | 56 | 45 | Weston | Weston County, Wyoming | B01003_001 | 2014 | 7135 | Weston, WY | 2398.09 |
| 28262 | 28263 | SKAGWAY | AK | 02230 | 2 | 230 | Skagway | Skagway Municipality, Alaska | B01003_001 | 2014 | 996 | Skagway, AK | 452.33 |
| 28263 | 28264 | HOONAH ANGOON | AK | 02105 | 2 | 105 | Hoonah Angoon | Hoonah-Angoon Census Area, Alaska | B01003_001 | 2014 | 2126 | Hoonah-Angoon, AK | 7524.92 |
| 28264 | 28265 | PETERSBURG | AK | 02195 | 2 | 195 | Petersburg | Petersburg Borough, Alaska | B01003_001 | 2014 | 3212 | Petersburg, AK | 3281.98 |
28265 rows × 13 columns
annual['DOSAGE_UNIT'] = annual['DOSAGE_UNIT'].astype(float)
mean_dose = annual.groupby(['year', 'BUYER_STATE'])['DOSAGE_UNIT'].mean().reset_index(name = "meandose")
mean_dose
fig = px.scatter(mean_dose, x="BUYER_STATE", y="meandose", color="year", title = "Mean Opioid Pills by State and Year", labels = {"meandose" : "Mean Opioid Pills", "BUYER_STATE" : "State", "year" : "Year"})
fig.show()